﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace FifaChamps.API.Data
{
    public static partial class DataManager
    {
        public static int Add_Group(int stageid, string name)
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.SP_Add_Group(stageid, name).First();
                return result.GroupID.Value;
            }
        }

        public static int Update_Group(int groupid, int stageID, string name)
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.SP_Update_Group(groupid, stageID, name).First();
                return result.GeasonID.Value;
            }
        }

        public static Group Get_Group(int groupID)
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Groups.Where(gr => gr.ID == groupID).First();
                return new Group(result.ID, result.StageID, result.GroupName);
            }
        }

        public static List<Group> Get_Groups(int stageID)
        {
            List<Group> list = new List<Group>();
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Groups.Where(gr => gr.StageID == stageID).ToList();
                result.ForEach(g => list.Add(new Group(g.ID, g.StageID, g.GroupName)));
            }

            return list;
        }

        public static int Add_GroupPlayer(int groupID, int playerID)
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.SP_Add_GroupPlayer(groupID, playerID, default(int?), default(int?), default(int?), default(int?), default(int?)).First();
                return result.GroupPlayerID.Value;
            }
        }

        public static GroupPlayer Get_GroupPlayer(int groupid, int playerID)
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Player_Groups.Where(ps => ps.GroupID == groupid && ps.PlayerID == playerID).First();
                return Get_GroupPlayer(result.ID);
            }
        }

        public static GroupPlayer Get_GroupPlayer(int group_playerid)
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Player_Groups.Where(ps => ps.ID == group_playerid).First();
                return new GroupPlayer(result.ID, result.GroupID, result.PlayerID, result.Position, result.Points, result.GamesPlayed, result.GoalsFor, result.GoalsAgainst);
            }
        }

        public static List<GroupPlayer> Get_GroupPlayers(int groupid)
        {
            List<GroupPlayer> groupplayers = new List<GroupPlayer>();

            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Player_Groups.Where(pg => pg.GroupID == groupid).ToList();
                result.ForEach( gp => groupplayers.Add( new GroupPlayer(gp.ID, gp.GroupID, gp.PlayerID, gp.Position, gp.Points, gp.GamesPlayed, gp.GoalsFor, gp.GoalsAgainst)));
            }

            return groupplayers;
        }

        public static int Update_GroupPlayer(int groupID, int playerID, int points, int gamesPlayed, int goalsFor, int goalsAgainst, int position)
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.FCT_Player_Groups.Where(sp => sp.GroupID == groupID && sp.PlayerID == playerID).First().ID;
                return Update_GroupPlayer(result, points, gamesPlayed, goalsFor, goalsAgainst, position);
            }
        }

        public static int Update_GroupPlayer(int group_playerid, int points, int gamesPlayed, int goalsFor, int goalsAgainst, int position)
        {
            using (var db = DataManager.FifaDB())
            {
                var result = db.SP_Update_GroupPlayer(group_playerid, position, points, gamesPlayed, goalsFor, goalsAgainst).First();
                return result.GroupPlayerID.Value;
            }
        }

        public static List<GroupFixture> Get_GroupFixtures(int groupID)
        {
            Dictionary<int, GroupFixture> list = new Dictionary<int, GroupFixture>();

            using (var db = DataManager.FifaDB())
            {
                var result = (from pg in db.FCT_Player_Groups
                              join pgr in db.FCT_PlayerGroup_FixtureResults
                              on pg.ID equals pgr.PlayerGroupID
                              join r in db.FCT_Fixture_Results
                              on pgr.FixtureResultID equals r.ID
                              join f in db.FCT_Fixtures
                              on r.FixtureID equals f.ID
                              where pg.GroupID == groupID
                              select new
                              {
                                  playergroup_resultid = pgr.ID,
                                  playergroupid = pgr.PlayerGroupID,
                                  fixtureresultid = r.ID,
                                  resulttype = r.ResultTypeID,
                                  goalsfor = r.GoalsFor,
                                  goalsagainst = r.GoalsAgainst,
                                  fixtureid = f.ID,
                                  fixturename = f.FixtureName,
                                  home = r.Home
                              });

                foreach (var item in result.ToList())
                {
                    GroupFixture group_fixture;
                    if (list.ContainsKey(item.fixtureid))
                    {
                        group_fixture = list[item.fixtureid];
                    }
                    else
                    {
                        group_fixture = new GroupFixture(item.fixtureid, item.fixturename);
                        list.Add(item.fixtureid, group_fixture);
                    }

                    group_fixture.Results.Add(new GroupResult(item.playergroup_resultid, item.playergroupid, item.fixtureresultid, item.resulttype, item.fixtureid, item.goalsfor, item.goalsagainst, item.home));
                }
            }

            return list.Values.ToList();
        }

        public static GroupFixture Get_GroupFixture(int fixtureID)
        {
            GroupFixture group_fixture = null;
            using (var db = DataManager.FifaDB())
            {
                var result = (from pg in db.FCT_Player_Groups
                              join pgr in db.FCT_PlayerGroup_FixtureResults
                              on pg.ID equals pgr.PlayerGroupID
                              join r in db.FCT_Fixture_Results
                              on pgr.FixtureResultID equals r.ID
                              join f in db.FCT_Fixtures
                              on r.FixtureID equals f.ID
                              where f.ID == fixtureID
                              select new
                              {
                                  playergroup_resultid = pgr.ID,
                                  playergroupid = pgr.PlayerGroupID,
                                  fixtureresultid = r.ID,
                                  resulttype = r.ResultTypeID,
                                  goalsfor = r.GoalsFor,
                                  goalsagainst = r.GoalsAgainst,
                                  fixtureid = f.ID,
                                  fixturename = f.FixtureName,
                                  home = r.Home
                              }).ToList();


                foreach (var item in result.ToList())
                {
                    if (group_fixture == null) group_fixture = new GroupFixture(item.fixtureid, item.fixturename);
                    group_fixture.Results.Add(new GroupResult(item.playergroup_resultid, item.playergroupid, item.fixtureresultid, item.resulttype, item.fixtureid, item.goalsfor, item.goalsagainst, item.home));
                }
            }

            return group_fixture;
        }
    }
}
